package com.cw.jeeyt.service.impl.system;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cw.jeeyt.common.enums.EnumDatabaseType;
import com.cw.jeeyt.common.enums.EnumMysqlDataType;
import com.cw.jeeyt.service.bo.system.SystemDatabaseBo;
import com.cw.jeeyt.service.bo.system.SystemDatabaseColumnBo;
import com.cw.jeeyt.service.bo.system.SystemDatabaseTableBo;
import com.cw.jeeyt.core.util.SessionUtil;
import com.cw.jeeyt.dao.intf.system.SystemDatabaseTableDao;
import com.cw.jeeyt.dao.model.system.SystemDatabaseTableDo;
import com.cw.jeeyt.service.converter.system.SystemDatabaseTableConverter;
import com.cw.jeeyt.service.intf.system.SystemDatabaseColumnService;
import com.cw.jeeyt.service.intf.system.SystemDatabaseService;
import com.cw.jeeyt.service.intf.system.SystemDatabaseTableService;
import com.cw.jeeyt.service.intf.system.SystemDictService;
import com.cw.lang.mybatis.dto.PageInfo;
import com.cw.lang.common.utils.SequenceTool;

/**
 * 数据库表信息业务实现类
 *
 * @author chenw
* @version 1.0.0
 */
@Service
public class SystemDatabaseTableServiceImpl implements SystemDatabaseTableService {

    private static final Logger logger = LoggerFactory.getLogger(SystemDatabaseTableServiceImpl.class);
    @Autowired
    private SystemDatabaseTableDao systemDatabaseTableDao;//数据库表信息数据库操作接口
    @Autowired
    private SystemDatabaseService systemDatabaseService;//数据库业务实现类
    @Autowired
    private SystemDatabaseColumnService systemDatabaseColumnService;//数据库表字段信息业务实现类
    @Autowired
    private SystemDictService systemDictService;//数据字典业务实现类

    /**
     * 新增SystemDatabaseTable
     *
     * @param systemDatabaseTableBo
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void insertSystemDatabaseTable(SystemDatabaseTableBo systemDatabaseTableBo) throws Exception {

        if (systemDatabaseTableBo == null) {
            logger.error("数据库表信息为空");
            return;
        }
        SystemDatabaseTableDo systemDatabaseTable = SystemDatabaseTableConverter.bo2Do(systemDatabaseTableBo);
        systemDatabaseTable.setId(SequenceTool.nextId());
        systemDatabaseTable.setCreateBy(SessionUtil.getSessionUserId());
        systemDatabaseTable.setCreateDate(new Date());
        systemDatabaseTable.setUpdateBy(SessionUtil.getSessionUserId());
        systemDatabaseTable.setUpdateDate(new Date());
        systemDatabaseTableDao.insertSystemDatabaseTable(systemDatabaseTable);
    }

    /**
     * 更新SystemDatabaseTable信息
     *
     * @param systemDatabaseTableBo
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void updateSystemDatabaseTable(SystemDatabaseTableBo systemDatabaseTableBo) throws Exception {

        if (systemDatabaseTableBo == null) {
            logger.error("数据库表信息为空");
            return;
        }
        SystemDatabaseTableDo systemDatabaseTable = SystemDatabaseTableConverter.bo2Do(systemDatabaseTableBo);
        systemDatabaseTable.setUpdateBy(SessionUtil.getSessionUserId());
        systemDatabaseTable.setUpdateDate(new Date());
        systemDatabaseTableDao.updateSystemDatabaseTable(systemDatabaseTable);
    }

    /**
     * 删除SystemDatabaseTable
     *
     * @param systemDatabaseTableBo
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void deleteSystemDatabaseTable(SystemDatabaseTableBo systemDatabaseTableBo) throws Exception {
        if (systemDatabaseTableBo == null) {
            logger.error("数据库表信息为空");
            return;
        }
        systemDatabaseTableDao.deleteSystemDatabaseTable(SystemDatabaseTableConverter.bo2Do(systemDatabaseTableBo));
        //删除数据库表
        systemDatabaseColumnService.deleteByTableId(systemDatabaseTableBo.getId());
    }

    /**
     * 分页查询SystemDatabaseTable
     *
     * @param systemDatabaseTableBo
     * @param startNum
     * @param pageSize
     * @return
     * @throws Exception
     */
    @Override
    public PageInfo<SystemDatabaseTableBo> queryByPage(SystemDatabaseTableBo systemDatabaseTableBo, Integer startNum,
        Integer pageSize) throws Exception {

        PageInfo<SystemDatabaseTableDo> pageInfo = systemDatabaseTableDao.queryByPage(
            SystemDatabaseTableConverter.bo2Do(systemDatabaseTableBo), startNum, pageSize);
        return new PageInfo<>(pageInfo, SystemDatabaseTableConverter.doList2BoList(pageInfo.getList()));
    }

    /**
     * 根据主键查询SystemDatabaseTable信息
     *
     * @param id
     * @return
     */
    @Override
    public SystemDatabaseTableBo getSystemDatabaseTableById(String id) throws Exception {

        return SystemDatabaseTableConverter.do2Bo(systemDatabaseTableDao.getSystemDatabaseTableById(id));
    }

    /**
     * 同步数据库表信息、表字段信息
     *
     * @param databaseId 数据库id
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void syncSystemDatabaseTable(String databaseId) throws Exception {
        if (StringUtils.isEmpty(databaseId)) {
            logger.info("--数据库id为空--");
            return;
        }
        SystemDatabaseBo systemDatabaseBo = systemDatabaseService.getSystemDatabaseById(databaseId);
        if (EnumDatabaseType.MYSQL.getCode()
            .equals(systemDatabaseBo.getDatabaseType())) {
            //同步表信息
            Connection connection = getDBConnection(systemDatabaseBo);
            try {
                String queryTableSql
                    = "select table_name tableCode,table_comment tableName from information_schema.tables where table_schema = ?";
                PreparedStatement pstmt = connection.prepareStatement(queryTableSql);
                pstmt.setString(1, systemDatabaseBo.getDatabaseCode());
                ResultSet rs = pstmt.executeQuery();
                while (rs.next()) {
                    SystemDatabaseTableDo systemDatabaseTable = new SystemDatabaseTableDo();
                    String tableCode = rs.getString("tableCode");
                    String tableName = rs.getString("tableName");
                    SystemDatabaseTableDo sdt = systemDatabaseTableDao.checkSystemDatabaseTable(tableCode, tableName,
                        systemDatabaseBo.getDatabaseCode());
                    if (sdt != null) {
                        continue;
                    }
                    Long userId = SessionUtil.getSessionUserId();
                    String id = SequenceTool.nextId();
                    systemDatabaseTable.setId(id);
                    systemDatabaseTable.setTableCode(tableCode);
                    systemDatabaseTable.setTableComment(tableName);
                    systemDatabaseTable.setTableName(tableName);
                    systemDatabaseTable.setCreateBy(userId);
                    systemDatabaseTable.setCreateDate(new Date());
                    systemDatabaseTable.setUpdateBy(userId);
                    systemDatabaseTable.setUpdateDate(new Date());
                    systemDatabaseTable.setDatabaseId(databaseId);
                    systemDatabaseTableDao.insertSystemDatabaseTable(systemDatabaseTable);
                    //同步表字段信息
                    StringBuilder queryColumnSql = new StringBuilder(
                        "select distinct column_name columnCode, data_type columnType, column_comment columnName,character_maximum_length columnLength,numeric_precision numericPercision,numeric_scale numericScale,column_key isPrimaryKey from information_schema.columns where table_name = ? and table_schema = ?");
                    PreparedStatement columnPstmt = connection.prepareStatement(queryColumnSql.toString());
                    columnPstmt.setString(1, tableCode);
                    columnPstmt.setString(2, systemDatabaseBo.getDatabaseCode());
                    ResultSet rsColumn = columnPstmt.executeQuery();
                    List<SystemDatabaseColumnBo> dataBaseColumnList = new ArrayList<SystemDatabaseColumnBo>();
                    while (rsColumn.next()) {
                        SystemDatabaseColumnBo sdcb = new SystemDatabaseColumnBo();
                        String columnCode = rsColumn.getString("columnCode");
                        String columnName = rsColumn.getString("columnName");
                        if (StringUtils.isNotEmpty(columnCode)) {
                            columnCode = columnCode.toLowerCase();
                        }
                        if (StringUtils.isNotEmpty(columnName)) {
                            if (columnName.length() > 100) {
                                columnName = columnName.substring(0, 100);
                            }
                        }
                        String columnType = rsColumn.getString("columnType");
                        sdcb.setId(SequenceTool.nextId());
                        sdcb.setColumnCode(columnCode);
                        sdcb.setColumnComment(columnName);
                        sdcb.setColumnName(columnName);
                        String columnLength = rsColumn.getString("columnLength");
                        String numericPercision = rsColumn.getString("numericPercision");
                        String numericScale = rsColumn.getString("numericScale");
                        sdcb.setColumnLength(columnLength);
                        if (StringUtils.isEmpty(columnLength)) {
                            sdcb.setColumnLength(numericPercision);
                            sdcb.setColumnPoint(numericScale);
                        }
                        String isPrimaryKey = rsColumn.getString("isPrimaryKey");//是否主键
                        sdcb.setIsPrimaryKey(isPrimaryKey);
                        EnumMysqlDataType mysqlDataType = EnumMysqlDataType.findByValue(columnType.toUpperCase());
                        if (mysqlDataType != null) {
                            String dataType = mysqlDataType.getCode();
                            if (EnumMysqlDataType.INT.getCode()
                                .equals(dataType)) {
                                sdcb.setColumnType(EnumMysqlDataType.INTEGER.getCode());
                            } else if (EnumMysqlDataType.DATETIME.getCode()
                                .equals(dataType)) {
                                sdcb.setColumnType(EnumMysqlDataType.DATETIME.getCode());
                            } else if (EnumMysqlDataType.TIMESTAMP.getCode()
                                .equals(dataType)) {
                                sdcb.setColumnType(EnumMysqlDataType.TIMESTAMP.getCode());
                            } else if (EnumMysqlDataType.TINYTEXT.getCode()
                                .equals(dataType) || EnumMysqlDataType.LONGTEXT.getCode()
                                .equals(dataType) || EnumMysqlDataType.BLOB.getCode()
                                .equals(dataType) || EnumMysqlDataType.TEXT.getCode()
                                .equals(dataType) || EnumMysqlDataType.MEDIUMTEXT.getCode()
                                .equals(dataType)) {
                                sdcb.setColumnType(EnumMysqlDataType.VARCHAR.getCode());
                            } else {
                                sdcb.setColumnType(dataType);
                            }
                        }
                        sdcb.setTableId(id);
                        sdcb.setCreateBy(userId);
                        sdcb.setCreateDate(new Date());
                        sdcb.setUpdateBy(userId);
                        sdcb.setUpdateDate(new Date());
                        dataBaseColumnList.add(sdcb);
                    }
                    if (dataBaseColumnList != null && !dataBaseColumnList.isEmpty()) {
                        systemDatabaseColumnService.insertSystemDatabaseColumnBat(dataBaseColumnList);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new Exception(e.getMessage());
            } finally {
                if (connection != null) {
                    connection.close();
                }
            }
        }
    }

    /**
     * 删除当前数据库下的表信息、表字段信息
     *
     * @param databaseId 数据库id
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void deleteByDatabaseId(String databaseId) throws Exception {

        if (StringUtils.isEmpty(databaseId)) {
            logger.info("--数据库id为空--");
            return;
        }
        //删除表下面的字段信息
        List<SystemDatabaseTableDo> systemDatabaseTableList = systemDatabaseTableDao.queryListByDatabaseId(databaseId);
        if (systemDatabaseTableList != null && !systemDatabaseTableList.isEmpty()) {
            for (SystemDatabaseTableDo sdt : systemDatabaseTableList) {
                systemDatabaseColumnService.deleteByTableId(sdt.getId());
            }
        }
        //删除表信息
        systemDatabaseTableDao.deleteByDatabaseId(databaseId);
    }

    /**
     * 获取数据库连接信息
     *
     * @param systemDatabaseBo
     * @return
     * @throws SQLException
     */
    private Connection getDBConnection(SystemDatabaseBo systemDatabaseBo) throws Exception {
        Connection conn = null;
        try {
            Class.forName(systemDatabaseBo.getJdbcDriverClass())
                .newInstance();
            conn = DriverManager.getConnection(systemDatabaseBo.getJdbcUrl(), systemDatabaseBo.getJdbcUserName(),
                systemDatabaseBo.getJdbcUserPassword() == null ? "" : systemDatabaseBo.getJdbcUserPassword());
            if (logger.isDebugEnabled()) {
                logger.debug("Succeed to get a DB connection: " + conn);
            }
        } catch (Exception e) {
            throw new Exception("数据库连接配置有误，请确认再同步：" + e.getMessage());
        }
        return conn;
    }

    @Override
    public PageInfo<SystemDatabaseTableBo> querySyncByPage(SystemDatabaseTableBo systemDatabaseTableBo, String moduleId,
        Integer startNum, Integer pageSize) {
        SystemDatabaseTableDo systemDatabaseTable = SystemDatabaseTableConverter.bo2Do(systemDatabaseTableBo);
        systemDatabaseTable.setModuleId(moduleId);
        PageInfo<SystemDatabaseTableDo> pageInfo = systemDatabaseTableDao.querySyncByPage(systemDatabaseTable, startNum,
            pageSize);
        return new PageInfo<>(pageInfo, SystemDatabaseTableConverter.doList2BoList(pageInfo.getList()));
    }

    @Override
    public List<SystemDatabaseTableBo> queryList() {
        return SystemDatabaseTableConverter.doList2BoList(systemDatabaseTableDao.querySystemDatabaseTableList());
    }
}
